-- common..p_InsItem 0,'P','IOPU','VT','AA','BLR','LOT1',10.0,10.0,10.0,200.0,10.0,10.0,0.0,'SUNNY','05/03/2011',240.0,'CMV'
use common
go
IF OBJECT_ID('dbo.p_InsItem') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.p_InsItem
    IF OBJECT_ID('dbo.p_InsItem') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.p_InsItem >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.p_InsItem >>>'
END
go
create proc p_InsItem
(
	@Parent				int,			-- parent id
	@Type				varchar(2),		-- customer type
	@ItemCode			varchar(50),	-- ItemCode
	@ItemName			varchar(30),	-- ItemName
	@ItemClass			varchar(10),	-- ItemClass
	@Location			varchar(30),	-- Location
	@LotNumber			varchar(30),	-- LotNumber
	@Length				decimal(10,3),	-- Length
	@Width				decimal(10,3),	-- Width
	@Measurement		varchar(10),	-- Measurement
	@Quantity			decimal(10,3),	-- Quantity
	@Units				decimal(10,3),	-- Unit
	@PurchasePrice		decimal(10,3),	-- Autowrap participant
	@SalesPrice			decimal(10,3),	-- PurchasePrice
	@ProfitMargin		decimal(10,3),	-- ProfitMargin
	@PurchasedFrom		varchar(50),	-- PurchasedFrom
 	@PurchasedDate		varchar(50) ,	-- PurchasedDate
	@Tax				decimal(10,3),	-- Tax
	@CompanyName		varchar(50)		-- CompanyName  data belongs to which CMP
) as
begin
	-- declare

    declare @DateUpdated	datetime	-- datetime when this row was updated
    declare @UserUpdated	varchar(15)	-- user name that updated this row
	declare @Msg 		varchar(255)	-- last error message
	declare @ErrVal		int		-- last error number
	declare @ItemId		int	
	-- initialize
	select @ErrVal=0
	select @DateUpdated=getdate()
	select @UserUpdated=suser_name()
	select @ItemId=0
	if @ItemId=0
	begin
	   exec common..p_GetNextIdentity "common","t_ItemMaster",1,@CompanyName,@ItemId output
	end
	if @Parent=0 
	select @Parent =@ItemId
	-- validate
	if @ItemId=0
	begin
		select @ErrVal = 99999,@Msg="No identity defined for common..t_ItemMaster"
		goto ErrorHandler
	end

	-- process
	
	--- if parent item insert same code should not be insert
	
	if @Type ='P'
	begin
		if exists( 	select 1 
					from common..t_ItemMaster
					where ItemCode=@ItemCode  )
		begin			
			select @Msg='Item Code exists in Item Code'
			select @ErrVal = 99999
			goto ErrorHandler	
		end
	end
	begin tran p_InsItem
		select @ErrVal=0
		if @ErrVal=0
		begin
  print 'insert started'
				insert into common..t_ItemMaster 
				( ItemId, Parent,Type,ItemCode,ItemName,ItemClass,Location,LotNumber,Length,
				  Width,Measurement,Quantity,Units,PurchasePrice,SalesPrice,ProfitMargin,PurchasedFrom,
				  PurchasedDate ,Tax,CompanyName)
				values(@ItemId, @Parent,@Type,@ItemCode,@ItemName,@ItemClass,@Location,@LotNumber,@Length,
				  @Width,@Measurement,@Quantity,@Units,@PurchasePrice,@SalesPrice,@ProfitMargin,@PurchasedFrom,
				  @PurchasedDate ,@Tax,@CompanyName)
	
				select @ErrVal=@@error
			
		end
		if @ErrVal!=0
		begin
			rollback tran p_InsItem
			select @ItemId=0
			select @ErrVal=99999,@Msg="Failed to insert new customer:" + @ItemCode
			goto ErrorHandler
		end
	commit tran p_InsItem
    --end

	if @ErrVal!=0
	begin
			select @ErrVal=99999, @Msg="Failed to insert ItemCode:" + @ItemCode
			goto ErrorHandler
	end
Done:

	return 0 

ErrorHandler:
	raiserror @ErrVal @Msg
end
go
EXEC sp_procxmode 'dbo.p_InsItem','unchained'
go
IF OBJECT_ID('dbo.p_InsItem') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.p_InsItem >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.p_InsItem >>>'
go
GRANT EXECUTE ON dbo.p_InsItem TO public
go